package net.shop.dal.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import net.shop.common.ShopPager;
import net.shop.dal.dao.ProductDao;
import net.shop.dal.entity.LangProduct;
import net.shop.dal.entity.Product;
import net.shop.dal.entity.ProductVirtualCategory;

@Repository
public class JdbcProductDao extends JdbcBaseDao<Product, Integer> implements ProductDao {

	private static final String COUNT_SQL = "SELECT COUNT(*) FROM web_product_info";
	private static final String INSERT_SQL = "INSERT INTO web_product_info(hase_code,category_id,provider_id,brand_id,product_sn,product_name,product_pic,sale_price,friendly_url,stock_num,rate_average,rate_reviews)VALUES(:haseCode,:categoryId,:providerId,:brandId,:productSn,:productName,:productPic,:salePrice,:friendlyUrl,:stockNum,:rateAverage,:rateReviews)";
	private static final String MODIFY_SQL = "UPDATE web_product_info SET product_id=:productId,hase_code=:haseCode,category_id=:categoryId,provider_id=:providerId,brand_id=:brandId,product_sn=:productSn,product_name=:productName,product_pic=:productPic,sale_price=:salePrice,friendly_url=:friendlyUrl,stock_num=:stockNum,rate_average=:rateAverage,rate_reviews=:rateReviews ";
	private static final String DELETE_SQL = "DELETE FROM web_product_info ";
	private static final String SELECT_SQL = "SELECT product_id,hase_code,category_id,provider_id,brand_id,product_sn,product_name,product_pic,sale_price,friendly_url,stock_num,rate_average,rate_reviews FROM web_product_info ";
	private static final String SELECT_JOIN_SQL = "SELECT p.product_id,p.hase_code,p.category_id,p.provider_id,p.brand_id,p.product_sn,p.product_name,p.product_pic,p.sale_price,p.friendly_url,p.stock_num,p.rate_average,p.rate_reviews,l.language_id,l.product_title,l.product_desc,l.meta_keywords,l.meta_desc FROM web_product_info AS p LEFT JOIN lang_product_info AS l ON l.language_id=? AND l.product_id=p.product_id ";

	private ParameterizedRowMapper<Product> mapper = new ParameterizedRowMapper<Product>() {
		public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
			int size = rs.getMetaData().getColumnCount();
			Product product = new Product();
			LangProduct lang = new LangProduct();
			
			product.setProductId(rs.getInt(1));
			product.setHaseCode(rs.getLong(2));
			product.setCategoryId(rs.getInt(3));
			product.setProviderId(rs.getShort(4));
			product.setBrandId(rs.getShort(5));
			product.setProductSn(rs.getString(6));
			product.setProductName(rs.getString(7));
			product.setProductPic(rs.getString(8));
			product.setSalePrice(rs.getDouble(9));
			product.setFriendlyUrl(rs.getString(10));
			product.setStockNum(rs.getInt(11));
			product.setRateAverage(rs.getFloat(12));
			product.setRateReviews(rs.getInt(13));
			
			if (size > 13) {
				lang.setProductId(product.getProductId());
				lang.setLanguageId(rs.getShort(14));
				lang.setProductTitle(rs.getString(15));
				lang.setProductDesc(rs.getString(16));
				lang.setMetaKeywords(rs.getString(17));
				lang.setMetaDesc(rs.getString(18));
			}
			product.setLang(lang);
			
			return product;
		}
	};

	public void save(Product product) {
		SqlParameterSource param = new BeanPropertySqlParameterSource(product);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		getSimpleJdbcTemplate().getNamedParameterJdbcOperations().update(INSERT_SQL, param, keyHolder);
		product.setProductId(keyHolder.getKey().intValue());		
	}

	public int delete(Integer productId) {
		String sql = DELETE_SQL +"WHERE product_id=?";
		return getSimpleJdbcTemplate().update(sql, productId);		
	}

	public int delete(Product product) {
		return delete(product.getProductId());
	}

	public int modify(Product product) {
		String sql = MODIFY_SQL +"WHERE product_id=:productId";
		return getSimpleJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(product));
	}

	public int getAllCount() {
		return getSimpleJdbcTemplate().queryForInt(COUNT_SQL);
	}

	public Product get(Integer productId) {
		String sql = SELECT_SQL +"WHERE product_id=?";
		Object[] args = new Object[]{productId};
		return (Product) getJdbcTemplate().queryForObject(sql, args, mapper);
	}

	public Product getWithLang(short languageId, int productId) {
		String sql = SELECT_JOIN_SQL +"WHERE p.product_id=?";
		Object[] args = new Object[]{languageId, productId};
		return (Product) getJdbcTemplate().queryForObject(sql, args, mapper);
	}
	
	public Product findByHaseCode(short languageId, long haseCode) {
		String sql = SELECT_JOIN_SQL +"WHERE p.hase_code=?";
		Object[] args = new Object[]{languageId, haseCode};
		return (Product) getJdbcTemplate().queryForObject(sql, args, mapper);
	}

	public ShopPager<Product> getVirtualCategoryProductList(ProductVirtualCategory category, ShopPager<Product> pager) {
		return getReallyCategoryProductList(category.getLang().getLanguageId(), category.getCategoryId(), pager);
	}

	public ShopPager<Product> getReallyCategoryProductList(short languageId, int categoryId, ShopPager<Product> pager) {
		String sql = SELECT_JOIN_SQL +"WHERE p.category_id=?";
		List<Product> products = getSimpleJdbcTemplate().query(sql, mapper, languageId, categoryId);
		pager.setResult(products);
		return pager;
	}
}
